﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using NangLuongTieuDiemDTO.DTO;

namespace NangLuongTieuDiemDAO.DAO
{
    public class LoaiSanPhamDAO
    {
        #region "[Insert]"
        /// <summary>
        /// Thêm 1 đối tượng loại sản phẩm
        /// </summary>
        /// <param name="lspDto">đối tượng loại sản phẩm cần thêm</param>
        /// <returns></returns>
        public static int Insert(LoaiSanPhamDTO lspDto)
        {
            int ID=-1;
            try
            {
                // Mo ket noi
                SqlConnection cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = "insert into LoaiSanPham(TenLoaiSanPham, Xoa, GhiChu,LoaiSanPhamChinhID) values(@TenLoaiSanPham ,@Xoa ,@GhiChu,@LoaiSanPhamChinhID)";

                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);


                cmd.Parameters.Add("@TenLoaiSanPham", SqlDbType.NVarChar);
                cmd.Parameters.Add("@Xoa", SqlDbType.Bit);
                cmd.Parameters.Add("@GhiChu", SqlDbType.NText);
                cmd.Parameters.Add("@LoaiSanPhamChinhID", SqlDbType.Int);

                cmd.Parameters["@TenLoaiSanPham"].Value = lspDto.TenLoaiSanPham;
                cmd.Parameters["@Xoa"].Value = lspDto.Xoa;
                cmd.Parameters["@GhiChu"].Value = lspDto.GhiChu;
                cmd.Parameters["@LoaiSanPhamChinhID"].Value = lspDto.LoaiSanPhamChinhID;

                cmd.ExecuteNonQuery();

                // lay ID cua dong insert

                strSQL = "Select @@IDENTITY";
                cmd = new SqlCommand(strSQL, cn);
                ID = Convert.ToInt32(cmd.ExecuteScalar());

                // dong ket noi csdl
                cn.Close();
            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("Insert", ex);
            }
            return ID;
        }
        #endregion

        #region "[Update]"
        /// <summary>
        /// cập nhật 1 đối tượng LoaiSanPham
        /// </summary>
        /// <param name="lspDto">đối tượng LoaiSanPham cần cập nhật</param>
        /// <returns></returns>
        public static bool Update(LoaiSanPhamDTO lspDto)
        {
            bool isSuccess = false;
            SqlConnection cn;
            try
            {
                // Mo ket noi
                cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = "Update LoaiSanPham Set TenLoaiSanPham = @TenLoaiSanPham ,Xoa = @Xoa , GhiChu = @GhiChu,LoaiSanPhamChinhID = @LoaiSanPhamChinhID where ID = @ID ";

                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);


                cmd.Parameters.Add("@TenLoaiSanPham", SqlDbType.NVarChar);
                cmd.Parameters.Add("@Xoa", SqlDbType.Bit);
                cmd.Parameters.Add("@GhiChu", SqlDbType.NText);
                cmd.Parameters.Add("@ID", SqlDbType.Int);
                cmd.Parameters.Add("@LoaiSanPhamChinhID", SqlDbType.Int);

                cmd.Parameters["@TenLoaiSanPham"].Value = lspDto.TenLoaiSanPham;
                cmd.Parameters["@Xoa"].Value            = lspDto.Xoa;
                cmd.Parameters["@GhiChu"].Value         = lspDto.GhiChu;
                cmd.Parameters["@ID"].Value             = lspDto.ID;
                cmd.Parameters["@LoaiSanPhamChinhID"].Value = lspDto.LoaiSanPhamChinhID;

                cmd.ExecuteNonQuery();

                // dong ket noi csdl
                cn.Close();
                isSuccess = true;

            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("Update", ex);
            }
            return isSuccess;
        }
        #endregion

        #region "[Delete]"
        /// <summary>
        /// Xóa 1 đối tượng LoaiSanPham
        /// </summary>
        /// <param name="ID">mã đối tượng LoaiSanPham cần xóa</param>
        /// <returns></returns>
        public static bool Delete(int ID)
        {
            bool isSuccess = false;
            SqlConnection cn;
            try
            {
                // Mo ket noi
                cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = " Update  LoaiSanPham Set Xoa = 'true' where ID = " + ID.ToString();


                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                //cmd.Parameters.Add("@ID", SqlDbType.Int);
                //cmd.Parameters["@ID"].Value = ID;


                cmd.ExecuteNonQuery();
                // dong ket noi csdl
                cn.Close();
                isSuccess = true;

            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("Delete", ex);
            }
            return isSuccess;
        }
        #endregion

        #region "[Get all]"
        /// <summary>
        /// lấy danh sách các đối tượng LoaiSanPham
        /// </summary>
        /// <returns></returns>
        public static List<LoaiSanPhamDTO> GetAll()
        {
   
            SqlConnection cn;

            // Mo ket noi
            cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

            // Chuoi sql thao tac csdl
            string strSQL = " Select * From LoaiSanPham"; //Cao Hoang An minh can bo dieu kien where xoa = 0 o day de load len comboxboxtimkiem, cac ban co the them dk xoa tren code


            // Thuc thi chuoi sql
            SqlCommand cmd = new SqlCommand(strSQL, cn);

            SqlDataReader dr = cmd.ExecuteReader();

            List<LoaiSanPhamDTO> ds_lspDto = new List<LoaiSanPhamDTO>();

            while (dr.Read() == true)
            {
                LoaiSanPhamDTO lspDto = new LoaiSanPhamDTO();
                lspDto.ID             = dr.GetInt32(0);
                lspDto.TenLoaiSanPham = dr.GetString(1);
                lspDto.Xoa            = dr.GetBoolean(2);
                lspDto.GhiChu         = dr.GetString(3);
                lspDto.LoaiSanPhamChinhID = int.Parse(dr["LoaiSanPhamChinhID"].ToString());

                ds_lspDto.Add(lspDto);
            }

            cn.Close();
            return ds_lspDto;
        }
        #endregion

        #region "[SelectByPrimaryKey]"
        /// <summary>
        /// Lấy 1 đối tượng LoaiSanPham
        /// </summary>
        /// <param name="ID">mã đối tượng LoaiSanPham</param>
        /// <returns></returns>
        public static LoaiSanPhamDTO SelectByPrimaryKey(int ID)
        {
            LoaiSanPhamDTO lspDto = null;
            try
            {
                SqlConnection cn;

                // Mo ket noi
                cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = " Select * From LoaiSanPham where ID ="+ID.ToString()+" and Xoa ='false'";
                //and Xoa = false

                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                //cmd.Parameters.Add("@ID", SqlDbType.Int);
                //cmd.Parameters["@ID"].Value = ID;

                SqlDataReader dr = cmd.ExecuteReader();
                lspDto = new LoaiSanPhamDTO();

                while (dr.Read() == true)
                {
                    lspDto.ID = dr.GetInt32(0);
                    lspDto.TenLoaiSanPham = dr.GetString(1);
                    lspDto.Xoa = dr.GetBoolean(2);
                    lspDto.GhiChu = dr.GetString(3);
                    lspDto.LoaiSanPhamChinhID = int.Parse(dr["LoaiSanPhamChinhID"].ToString());
                }

                // Dong ket noi csdl
                cn.Close();
            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("SelectByPrimaryKey", ex);
            }
            return lspDto;
        }
        #endregion

        #region "[SelectByLoaiSanPhamChinhID]"
        public static List<LoaiSanPhamDTO> SelectByLoaiSanPhamChinhID(int ID)
        {
            List<LoaiSanPhamDTO> ds_lspDto = new List<LoaiSanPhamDTO>();
            try
            {
                SqlConnection cn;

                // Mo ket noi
                cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = " Select * From LoaiSanPham where LoaiSanPhamChinhID =" + ID.ToString() + " and Xoa ='false'";
                //and Xoa = false

                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                //cmd.Parameters.Add("@ID", SqlDbType.Int);
                //cmd.Parameters["@ID"].Value = ID;

                SqlDataReader dr = cmd.ExecuteReader();
                

                while (dr.Read() == true)
                {
                    LoaiSanPhamDTO lspDto = new LoaiSanPhamDTO();
                    lspDto.ID = dr.GetInt32(0);
                    lspDto.TenLoaiSanPham = dr.GetString(1);
                    lspDto.Xoa = dr.GetBoolean(2);
                    lspDto.GhiChu = dr.GetString(3);
                    lspDto.LoaiSanPhamChinhID = int.Parse(dr["LoaiSanPhamChinhID"].ToString());
                    ds_lspDto.Add(lspDto);
                }

                // Dong ket noi csdl
                cn.Close();
            }
            catch (Exception ex)
            {
                InSert_LoiHeThong("SelectByLoaiSanPhamChinhID", ex);
            }
            return ds_lspDto;
        }
        #endregion


        #region "[Select By Name]"
        public static LoaiSanPhamDTO SelectByName(string _strNameProductType)
        {
            LoaiSanPhamDTO objResult = new LoaiSanPhamDTO();
            try
            {
                SqlConnection cn;

                // Mo ket noi
                cn = NangLuongTieuDiemDAO.DataHelper.SqlDataHelper.ConnectionData();

                // Chuoi sql thao tac csdl
                string strSQL = "  select * from LoaiSanPham where TenLoaiSanPham=N'" + _strNameProductType.Trim() + "' and Xoa ='False'";
                //and Xoa = false

                // Thuc thi chuoi sql
                SqlCommand cmd = new SqlCommand(strSQL, cn);

                SqlDataReader dr = cmd.ExecuteReader();


                while (dr.Read() == true)
                {
                    objResult = new LoaiSanPhamDTO();
                    objResult.ID = dr.GetInt32(0);
                    objResult.TenLoaiSanPham = dr.GetString(1);
                    objResult.Xoa = dr.GetBoolean(2);
                    objResult.GhiChu = dr.GetString(3);
                    objResult.LoaiSanPhamChinhID = int.Parse(dr["LoaiSanPhamChinhID"].ToString());
                }

                // Dong ket noi csdl
                cn.Close();
            }
            catch { }
            return objResult;
        }
        #endregion


        #region "[Hàm insert lỗi hệ thống]"
        public static void InSert_LoiHeThong(string TenHam, Exception ex)
        {
            LoiHeThongDTO lhtDto = new LoiHeThongDTO();
            lhtDto.TenLoi = ex.Message.ToString();
            lhtDto.NgayTao = DateTime.Now;
            lhtDto.TenHamBiLoi = "LoaiSanPhamDAO_" + TenHam;
            lhtDto.SuaLoi = false;
            lhtDto.GhiChu = "";
            LoiHeThongDAO.Insert(lhtDto);

        }
        #endregion

    }
}
